from tools.dbo import get_server_data_car, put_mysql_data, get_server_data, carried_sql, get_server_data_one, \
    get_dict_data_sql


# 项目分类迁移,项目拓展字段新增迁移
def syn_project_type(sql_server_conn, mysql_conn):
    print("正在迁移项目分类")
    carried_sql(mysql_conn,"TRUNCATE project_type_configuration")
    return_data = get_server_data(sql_server_conn,"2,id,projectTypeNo,projectTypeNm,mark","isDel = 0", "AP_PJ_ProjectType")
    if len(return_data) == 0:
        carried_sql(mysql_conn,"INSERT INTO `project_type_configuration`(`id`, `name`, `code`, `remark`, `is_delete`, `create_id`, `create_time`, `update_id`, `update_time`, `tenant_id`) VALUES (1, '默认项目类型', 'DEFAULT', NULL, 0, NULL, '2021-10-14 10:51:16', NULL, NULL, 2);")
    print(put_mysql_data(mysql_conn, "tenant_id,id,code,name,remark", "project_type_configuration", return_data))
    return_data = get_server_data(sql_server_conn, "2,projectTypeId,customFieldId,fieldNm,0", "isDel = 0",
                                  "AP_PJ_ProjectTypeExAttribute")
    print(put_mysql_data(mysql_conn, "tenant_id,project_type_id,cus_id,group_name,is_delete", "project_type_configuration_ex", return_data))
    print("项目分类迁移完成")

# 基础数据移动
def syn_project_member(sql_server_conn, mysql_conn):
    sql = "select * from project_member"
    return_data = get_server_data_car(mysql_conn, sql)
    list1 = []
    index = 1
    for index in range(0,52):
        index = index + 1
        for item in return_data:
            l = (index,item[2],item[3],item[4],item[5],item[6],item[7],item[8])
            list1.append(l)
    print(list1)
    print(put_mysql_data(mysql_conn, "project_id,user_id,user_name,role_ids,role_names,dept_id,dept_name,is_delete", "project_member", list1))

# 项目信息同步
def sys_project(sql_server_conn, mysql_conn):
    print("===============================正在迁移项目数据==============================")
    carried_sql(mysql_conn,"TRUNCATE project_info")

    return_data = get_server_data(sql_server_conn,
                                  "id,projectNo,projectNo,projectNm,departId+99,principalEmpId,projectTypeId,mark,projectPermissionType,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt,2",
                                  "1 = 1", "AP_PJ_Project")
    print(put_mysql_data(mysql_conn,
                         "id,cd,code,name,dept_id,leader_id,project_type_id,remark,data_scope,is_delete,create_id,create_time,update_id,update_time,tenant_id",
                         "project_info", return_data))
    update_type_name = """
    update project_info pi
    inner join project_type_configuration pt on pi.project_type_id=pt.id
    set pi.project_type_name=pt.name
    """
    carried_sql(mysql_conn, update_type_name)
    update_leader_name = """
    update project_info pi
    inner join sys_user u on pi.leader_id=u.user_id
    set pi.leader_name=u.user_name
    """
    carried_sql(mysql_conn, update_leader_name)
    update_dept_name = """
    update project_info pi
    inner join sys_dept d on pi.dept_id=d.dept_id
    set pi.dept_name=d.dept_name
    """
    carried_sql(mysql_conn, update_dept_name)

    update_project_state = """
      update project_info pi
      set pi.state=3 where pi.state is null
      """
    carried_sql(mysql_conn, update_project_state)
    print("===============================项目数据迁移完成==============================")


def sys_project_sample_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本项目数据==============================")
    select_project_sample_type = """
    select pj.projectId,st.id,st.sampleTypeNm from AP_PJ_Project2SampleType pj left join AP_SP_SampleType st 
    on pj.sampleTypeId = st.id
    """
    return_data = get_server_data_car(sql_server_conn,select_project_sample_type)
    dict={}
    for data in return_data:
        if not data[0] in dict.keys():
            dict[data[0]]={}
        dict[data[0]][data[1]]=data[2]
    res = []
    for item in dict.items():
        temp = [str(i) for i in item[1].keys()]
        sampleTypeIds = ",".join(temp)
        sampleTypeNms = ",".join(item[1].values())
        temp = ["" for i in range(len(item[1].keys()))]
        tempStr = ",".join(temp)
        res.append([sampleTypeIds,sampleTypeNms,tempStr,tempStr,tempStr,tempStr,item[0]])
    update_project_sample_type="update project_info set sample_type_id=%s,sample_type_name=%s,inspection_project_id=%s,inspection_project_name=%s,inspection_method_id=%s,inspection_method_name=%s where id=%s"
    cursor = mysql_conn.cursor()
    cursor.executemany(update_project_sample_type, res)
    mysql_conn.commit()
    lines = cursor.rowcount
    print(lines)
    print("===============================正在迁移样本项目完成==============================")


def sys_project_ex_value(sql_server_conn, mysql_conn):
    print("===============================正在迁移项目扩展字段信息数据==============================")
    carried_sql(mysql_conn,"TRUNCATE project_info_ex_value")
    project_type_ex_metrix = get_project_type_ex_metrix(sql_server_conn)
    select_project_type_ex_sql = "select * from AP_PJ_ProjectTypeExData"
    cus_value_dict = get_dict_data_sql(sql_server_conn, select_project_type_ex_sql)
    sample_resource_ex_value_list = []
    for item in cus_value_dict:
        if not item["projectTypeId"] in project_type_ex_metrix.keys():
            continue
        fields = project_type_ex_metrix[item["projectTypeId"]]
        for k, v in fields.items():
            if item[k] is None or item[k] == "":
                continue
            sample_resource_ex_value_list.append([item["projectId"],v,item[k],2])
    print(len(sample_resource_ex_value_list))
    print(put_mysql_data(mysql_conn, "project_info_id,cus_id,cus_value,tenant_id", "project_info_ex_value", sample_resource_ex_value_list))
    print("===============================项目字段信息数据迁移完成==============================")

# 项目附加信息倒排矩阵
def get_project_type_ex_metrix(sql_server_conn):
    sql = "select projectTypeId,customFieldId,fieldNo from AP_PJ_ProjectTypeExAttribute where isDel = 0"
    return_data = get_server_data_car(sql_server_conn, sql)
    res = {}
    for item in return_data:
        tmp = {}
        if item[0] in res.keys():
            tmp = res.get(item[0])
        tmp[item[2]] = item[1]
        res[item[0]] = tmp
    return res

def syn_project_team(sql_server_conn, mysql_conn):
    print("===============================正在迁移项目成员数据==============================")
    carried_sql(mysql_conn,"TRUNCATE project_member")
    return_data = get_server_data(sql_server_conn,
                                  "projectId,teamMemberId,teamMemberName,isrtEmpId,isrtDt,updtEmpId,updtDt,2",
                                  "1 = 1", "AP_PJ_ProjectTeam")
    print(put_mysql_data(mysql_conn,
                         "project_id,user_id,user_name,create_id,create_time,update_id,update_time,tenant_id",
                         "project_member", return_data))
    return_data = get_server_data_car(mysql_conn,"select u.user_id,u.dept_id,d.dept_name from sys_user u left join sys_dept d on u.dept_id = d.dept_id")
    for r in return_data:
        carried_sql(mysql_conn, "update project_member set dept_id = '"+str(r[1])+"',dept_name = '"+str(r[2])+"' where user_id = '"+str(r[0])+"'")
    carried_sql(mysql_conn, "update project_member set role_ids = '4',role_names = '生物样本库角色'")
    carried_sql(mysql_conn, "update project_member set role_ids = '1,2,3,4',role_names = '管理员,普通角色,审批角色,生物样本库角色' where user_id = '1'")

    print("===============================项目成员数据迁移完成==============================")
